﻿using Aspose.Cells;
using QJY.API;
using QJY.Common;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Web;
using System.Web.UI;

namespace LotusBIWeb
{
    public partial class TJGLZSTH : Page
    {
        public string strZID
        {
            get { return Request.QueryString["ZID"] ?? "4113"; }
        }
        public string strFormCode
        {
            get { return Request.QueryString["FormCode"] ?? "XTGG"; }
        }

        public TJModel TJDATA = new TJModel();

        protected void Page_Load(object sender, EventArgs e)
        {

            if (!IsPostBack)
            {


                string qxcode = Request.Cookies["qxcode"].Value.ToString();
                qxcode = qxcode.Replace("%2C", ",");
                string strSQL = " SELECT TypeNO FROM [dbo].[jh_auth_zidian] WHERE [Class] LIKE '%价区%' ";
                DataTable dtJQ = new JH_Auth_BranchB().GetDTByCommand(strSQL);
                ddlJQ.DataSource = dtJQ;
                this.ddlJQ.DataTextField = dtJQ.Columns["TypeNO"].ToString();
                this.ddlJQ.DataValueField = dtJQ.Columns["TypeNO"].ToString();
                ddlJQ.DataBind();
                ddlJQ.Items.Insert(0, "全部价区");

                txtStart.Text = DateTime.Now.AddMonths(-1).ToString("yyyy-MM-dd");
                txtEnd.Text = DateTime.Now.ToString("yyyy-MM-dd");

                DataTable dt = GetData(qxcode);
                this.repData.DataSource = dt;
                //显示数据
                this.repData.DataBind();
            }
        }




        protected void btExport_Click(Object sender, EventArgs e)
        {
            //DataSet ds = LoadDataSet();//使用DataSet对象
            //List<Customers> entity = GetCustomers();//使用实体类对象
            // DataTable dt = GetCustomersTable();//使用DataTable对象
            string qxcode = Request.Cookies["qxcode"].Value.ToString();
            qxcode = qxcode.Replace("%2C", ",");
            DataTable dt = GetData(qxcode);
            dt.TableName = "DTDATA";
            //创建一个workbookdesigner对象
            WorkbookDesigner designer = new WorkbookDesigner();

            //制定报表模板
            string path = HttpContext.Current.Request.MapPath("/");

            string mbpath = path + "/ViewV5/AppPage/SFJS/STH/QJDZMB.xlsx";
            designer.Open(mbpath);


            //设置DataSet对象
            //designer.SetDataSource(ds);

            //设置实体类对象
            // designer.SetDataSource("TJDATA", TJDATA);

            //设置Datatable对象
            designer.SetDataSource(UpdateDataTable(dt));

            ////设置变量对象
            designer.SetDataSource("ZNAME", TJDATA.ZNAME);
            designer.SetDataSource("DNAME", TJDATA.DNAME);
            designer.SetDataSource("DCTITLE", TJDATA.Year + "年" + DDLGJ.SelectedValue + "灌全局各站水量水费汇总表");



            designer.SetDataSource("TJDATE", TJDATA.TJDATE);
            designer.SetDataSource("LYJG", TJDATA.LYJG);
            designer.SetDataSource("JJJG", TJDATA.JJJG);

            ////设置集合变量
            //designer.SetDataSource("MultiVariable", new string[] { "Variable 1", "Variable 2", "Variable 3" });


            //根据数据源处理生成报表内容
            designer.Process();

            //保存Excel文件
            string strFileName = "DDMBdata" + DateTime.Now.ToString("yyMMddHHss") + ".xls";
            string fileToSave = path + "/ViewV5/AppPage/SFJS/" + strFileName;
            if (File.Exists(fileToSave))
            {
                File.Delete(fileToSave);
            }

            designer.Save(fileToSave, FileFormatType.Excel2003);


            Response.ContentType = "application/x-zip-compressed";
            Response.AddHeader("Content-type", "text/html;charset=UTF-8");
            Response.AddHeader("Content-Disposition", "attachment;filename=" + strFileName);
            //string filename = Server.MapPath("/" + attch.FileUrl);
            Response.TransmitFile(fileToSave);

            //打开Excel文件

        }


        public DataTable GetData(string qxcode)
        {
            DataTable dt = new DataTable();
            string strZNAME = "";
            string strJQWhere = "";
            if (ddlJQ.SelectedValue != "全部价区")
            {
                strJQWhere = "AND JQName='" + ddlJQ.SelectedValue + "'";
            }
            if (ddlzfzt.SelectedValue != "")
            {
                strJQWhere = "AND zfzt='" + ddlzfzt.SelectedValue + "'";
            }
            if (ddlzffs.SelectedValue != "")
            {
                strJQWhere = "AND zffs='" + ddlzffs.SelectedValue + "'";
            }
            string strWhere = strJQWhere + " AND   ZID IN ('"+ qxcode.ToFormatLike() + "')  AND   vwSBData.CRDate BETWEEN '" + txtStart.Text.ToString() + " 00:00:00' AND  '" + txtEnd.Text.ToString() + " 23:59:59'";
            string strSQL = " SELECT ID=(ROW_NUMBER() OVER(ORDER BY(SELECT 0))) ,JH_Auth_Branch.DeptRoot,DeptShort, GLZNAME,JQName,GYSF,QJSF,'0' AS LYSLXJ,'0' AS JJSLXJ , MAX(LYGYSJ) as LYGYSJ,MAX(LYSJ1) as LYSJ1,MAX(LYSJ2) as LYSJ2, MAX(JJGYSJ) as JJGYSJ,MAX(JJSJ1) as JJSJ1,MAX(JJSJ2) as JJSJ2,SUM(Convert(decimal(18,1),LYMS)) LYMS, SUM(Convert(decimal(18,2),LYSL1)) LYSL1, SUM(Convert(decimal(18,2),LYSL2)) LYSL2, SUM(Convert(decimal(18,2),LYSL3)) LYSL3, SUM(Convert(decimal(18,2),LYSL4)) LYSL4 , SUM(Convert(decimal(18,1),JJMS)) JJMS, SUM(Convert(decimal(18,2),JJSL1)) JJSL1, SUM(Convert(decimal(18,2),JJSL2)) JJSL2, SUM(Convert(decimal(18,2),JJSL3)) JJSL3, SUM(Convert(decimal(18,2),JJSL4)) JJSL4, SUM(Convert(decimal(18,2),ZJE)) ZJE, SUM(Convert(decimal(18,2),GYZJE)) GYZJE FROM vwSBData  LEFT JOIN JH_Auth_Branch ON vwSBData.GLZNAME=JH_Auth_Branch.DeptName  where 1=1 " + strWhere + " GROUP BY DeptRoot,DeptShort, GLZNAME,JQName,GYSF,QJSF ORDER BY JH_Auth_Branch.DeptRoot,DeptShort,GLZNAME,JQName DESC";
            dt = new JH_Auth_BranchB().GetDTByCommand(strSQL);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                decimal zje = decimal.Parse(dt.Rows[i]["ZJE"].ToString());
                decimal GYSF = decimal.Parse(dt.Rows[i]["GYZJE"].ToString());
                dt.Rows[i]["GYSF"] = Math.Round(GYSF, 2);
                dt.Rows[i]["QJSF"] = Math.Round(zje - GYSF, 2);

                dt.Rows[i]["LYSL1"] = Math.Round(decimal.Parse(dt.Rows[i]["LYSL1"].ToString()), 0, MidpointRounding.AwayFromZero).ToString();
                dt.Rows[i]["LYSL2"] = Math.Round(decimal.Parse(dt.Rows[i]["LYSL2"].ToString()), 0, MidpointRounding.AwayFromZero).ToString();
                dt.Rows[i]["LYSL3"] = Math.Round(decimal.Parse(dt.Rows[i]["LYSL3"].ToString()), 0, MidpointRounding.AwayFromZero).ToString();
                dt.Rows[i]["LYSL4"] = Math.Round(decimal.Parse(dt.Rows[i]["LYSL4"].ToString()), 0, MidpointRounding.AwayFromZero).ToString();

                dt.Rows[i]["JJSL1"] = Math.Round(decimal.Parse(dt.Rows[i]["JJSL1"].ToString()), 0, MidpointRounding.AwayFromZero).ToString();
                dt.Rows[i]["JJSL2"] = Math.Round(decimal.Parse(dt.Rows[i]["JJSL2"].ToString()), 0, MidpointRounding.AwayFromZero).ToString();
                dt.Rows[i]["JJSL3"] = Math.Round(decimal.Parse(dt.Rows[i]["JJSL3"].ToString()), 0, MidpointRounding.AwayFromZero).ToString();
                dt.Rows[i]["JJSL4"] = Math.Round(decimal.Parse(dt.Rows[i]["JJSL4"].ToString()), 0, MidpointRounding.AwayFromZero).ToString();


                dt.Rows[i]["LYSLXJ"] = decimal.Parse(dt.Rows[i]["LYSL1"].ToString()) + decimal.Parse(dt.Rows[i]["LYSL2"].ToString()) + decimal.Parse(dt.Rows[i]["LYSL3"].ToString()) + decimal.Parse(dt.Rows[i]["LYSL4"].ToString());
                dt.Rows[i]["JJSLXJ"] = decimal.Parse(dt.Rows[i]["JJSL1"].ToString()) + decimal.Parse(dt.Rows[i]["JJSL2"].ToString()) + decimal.Parse(dt.Rows[i]["JJSL3"].ToString()) + decimal.Parse(dt.Rows[i]["JJSL4"].ToString());

            }


            TJDATA.TJDATE = txtStart.Text + "至" + txtEnd.Text;
            TJDATA.ZNAME = strZNAME;

            TJDATA.GJ = DDLGJ.SelectedValue;
            TJDATA.Year = txtEnd.Text.ToString().Substring(0, 4);
            TJDATA.DCTITLE = TJDATA.Year + "年" + DDLGJ.SelectedValue + "灌全局各站水量水费汇总表";
            int intZID = int.Parse(strZID);
          

            #region 第一次修改报表
            dt.Columns.Add("LYSF0");
            dt.Columns.Add("LYSF1");
            dt.Columns.Add("LYSF2");
            dt.Columns.Add("LYSFXJ");

            dt.Columns.Add("JJSF0");
            dt.Columns.Add("JJSF1");
            dt.Columns.Add("JJSF2");
            dt.Columns.Add("JJSFXJ");
            dt.Columns.Add("RootName");

            for (int i = 0; i < dt.Rows.Count; i++)
            {

                string strGLZName = dt.Rows[i]["GLZName"].ToString();
                if (strGLZName.Contains("/"))
                {
                    dt.Rows[i]["RootName"] = strGLZName.Split('/')[0];
                    dt.Rows[i]["GLZNAME"] = strGLZName.Split('/')[1];
                }
                else
                {
                    dt.Rows[i]["RootName"] = strGLZName;
                    dt.Rows[i]["GLZNAME"] = strGLZName;
                }



                dt.Rows[i]["LYSF0"] = Math.Round((decimal.Parse(dt.Rows[i]["LYSL1"].ToString()) + decimal.Parse(dt.Rows[i]["LYSL2"].ToString())) * decimal.Parse(dt.Rows[i]["LYGYSJ"].ToString()), 3, MidpointRounding.AwayFromZero);//国营水价
                dt.Rows[i]["LYSF1"] = Math.Round(decimal.Parse(dt.Rows[i]["LYSL1"].ToString()) * decimal.Parse(dt.Rows[i]["LYSJ1"].ToString()), 3, MidpointRounding.AwayFromZero);
                dt.Rows[i]["LYSF2"] = Math.Round(decimal.Parse(dt.Rows[i]["LYSL2"].ToString()) * decimal.Parse(dt.Rows[i]["LYSJ2"].ToString()), 3, MidpointRounding.AwayFromZero);
                dt.Rows[i]["LYSFXJ"] = Math.Round(decimal.Parse(dt.Rows[i]["LYSF0"].ToString()) + decimal.Parse(dt.Rows[i]["LYSF1"].ToString()) + decimal.Parse(dt.Rows[i]["LYSF2"].ToString()), 3, MidpointRounding.AwayFromZero);



                if (dt.Rows[i]["LYSL2"].ToString() == "0")
                {
                    dt.Rows[i]["LYSF0"] = Math.Round(decimal.Parse(dt.Rows[i]["LYSF0"].ToString()), 2, MidpointRounding.AwayFromZero).ToString();
                    dt.Rows[i]["LYSF1"] = decimal.Parse(dt.Rows[i]["LYSFXJ"].ToString()) - decimal.Parse(dt.Rows[i]["LYSF0"].ToString());
                    dt.Rows[i]["LYSF2"] = 0;

                }
                else
                {
                    dt.Rows[i]["LYSF0"] = Math.Round(decimal.Parse(dt.Rows[i]["LYSF0"].ToString()), 2, MidpointRounding.AwayFromZero).ToString();
                    dt.Rows[i]["LYSF1"] = Math.Round(decimal.Parse(dt.Rows[i]["LYSF1"].ToString()), 2, MidpointRounding.AwayFromZero).ToString();
                    dt.Rows[i]["LYSF2"] = Math.Round(decimal.Parse(dt.Rows[i]["LYSFXJ"].ToString()) - decimal.Parse(dt.Rows[i]["LYSF0"].ToString()) - decimal.Parse(dt.Rows[i]["LYSF1"].ToString()), 2, MidpointRounding.AwayFromZero).ToString();

                }






                dt.Rows[i]["JJSF0"] = Math.Round((decimal.Parse(dt.Rows[i]["JJSL1"].ToString()) + decimal.Parse(dt.Rows[i]["JJSL2"].ToString())) * decimal.Parse(dt.Rows[i]["JJGYSJ"].ToString()), 3, MidpointRounding.AwayFromZero);//国营水价
                dt.Rows[i]["JJSF1"] = Math.Round(decimal.Parse(dt.Rows[i]["JJSL1"].ToString()) * decimal.Parse(dt.Rows[i]["JJSJ1"].ToString()), 3, MidpointRounding.AwayFromZero);
                dt.Rows[i]["JJSF2"] = Math.Round(decimal.Parse(dt.Rows[i]["JJSL2"].ToString()) * decimal.Parse(dt.Rows[i]["JJSJ2"].ToString()), 3, MidpointRounding.AwayFromZero);
                dt.Rows[i]["JJSFXJ"] = Math.Round(decimal.Parse(dt.Rows[i]["JJSF0"].ToString()) + decimal.Parse(dt.Rows[i]["JJSF1"].ToString()) + decimal.Parse(dt.Rows[i]["JJSF2"].ToString()), 3, MidpointRounding.AwayFromZero);


                if (dt.Rows[i]["JJSL2"].ToString() == "0")
                {
                    dt.Rows[i]["JJSF0"] = Math.Round(decimal.Parse(dt.Rows[i]["JJSF0"].ToString()), 2, MidpointRounding.AwayFromZero).ToString();
                    dt.Rows[i]["JJSF1"] = Math.Round(decimal.Parse(dt.Rows[i]["JJSFXJ"].ToString()) - decimal.Parse(dt.Rows[i]["JJSF0"].ToString()), 2, MidpointRounding.AwayFromZero).ToString();
                    dt.Rows[i]["JJSF2"] = 0;
                }
                else
                {
                    dt.Rows[i]["JJSF0"] = Math.Round(decimal.Parse(dt.Rows[i]["JJSF0"].ToString()), 2, MidpointRounding.AwayFromZero).ToString();
                    dt.Rows[i]["JJSF1"] = Math.Round(decimal.Parse(dt.Rows[i]["JJSF1"].ToString()), 2, MidpointRounding.AwayFromZero).ToString();
                    dt.Rows[i]["JJSF2"] = Math.Round(decimal.Parse(dt.Rows[i]["JJSFXJ"].ToString()) - decimal.Parse(dt.Rows[i]["JJSF0"].ToString()) - decimal.Parse(dt.Rows[i]["JJSF1"].ToString()), 2, MidpointRounding.AwayFromZero).ToString();

                }


                dt.Rows[i]["ZJE"] = Math.Round(decimal.Parse(dt.Rows[i]["JJSFXJ"].ToString()) + decimal.Parse(dt.Rows[i]["LYSFXJ"].ToString()), 2, MidpointRounding.AwayFromZero);
                dt.Rows[i]["GYSF"] = Math.Round(decimal.Parse(dt.Rows[i]["LYSF0"].ToString()) + decimal.Parse(dt.Rows[i]["JJSF0"].ToString()), 2, MidpointRounding.AwayFromZero);
                dt.Rows[i]["QJSF"] = Math.Round(decimal.Parse(dt.Rows[i]["ZJE"].ToString()) - decimal.Parse(dt.Rows[i]["GYSF"].ToString()), 2, MidpointRounding.AwayFromZero);



            }
            #endregion

            string[] colsHeJi = new string[] { "LYMS", "LYSL1", "LYSL2", "LYSLXJ", "LYSF0", "LYSF1", "LYSF2", "LYSFXJ", "JJMS", "JJSL1", "JJSL2", "JJSLXJ", "JJSF0", "JJSF1", "JJSF2", "JJSFXJ", "GYSF", "QJSF", "ZJE" };

            CommonHelp.dbDataTableSubSumRowsWithColList(dt, "JQName", "RootName", colsHeJi);


            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dt.Rows[i]["LYSFXJ"] = Math.Round(decimal.Parse(dt.Rows[i]["LYSFXJ"].ToString()), 2, MidpointRounding.AwayFromZero).ToString();
                dt.Rows[i]["JJSFXJ"] = Math.Round(decimal.Parse(dt.Rows[i]["JJSFXJ"].ToString()), 2, MidpointRounding.AwayFromZero).ToString();
                dt.Rows[i]["ZJE"] = Math.Round(decimal.Parse(dt.Rows[i]["ZJE"].ToString()), 2, MidpointRounding.AwayFromZero).ToString();

            }


            return dt;
        }

        protected void btQuery_Click(object sender, EventArgs e)
        {
            string qxcode = Request.Cookies["qxcode"].Value.ToString();
            qxcode = qxcode.Replace("%2C", ",");
            DataTable dt = GetData(qxcode);
            this.repData.DataSource = dt;
            //显示数据
            this.repData.DataBind();


        }



        private DataTable UpdateDataTable(DataTable argDataTable)
        {
            DataTable dtResult = new DataTable();

            List<string> LISTTYPE = new List<string>() { "LYMS", "LYYSSC", "LYSL1", "LYSL2", "LYSL3", "LYSL4", "LYSLXJ", "JJMS", "JJYSSC", "JJSL1", "JJSL2", "JJSL3", "JJSL4", "JJSLXJ", "GYSF", "QJSF", "ZJE", "LYSF0", "LYSF1", "LYSF2", "LYSFXJ", "JJSF0", "JJSF1", "JJSF2", "JJSFXJ" };
            //克隆表结构
            dtResult = argDataTable.Clone();
            foreach (DataColumn col in dtResult.Columns)
            {
                if (LISTTYPE.Contains(col.ColumnName))
                {
                    //修改列类型
                    col.DataType = typeof(decimal);
                }
            }
            foreach (DataRow row in argDataTable.Rows)
            {
                DataRow rowNew = dtResult.NewRow();
                rowNew["ID"] = row["ID"];
                rowNew["GLZNAME"] = row["GLZNAME"];
                rowNew["LYMS"] = row["LYMS"];
                rowNew["LYSL1"] = row["LYSL1"];
                rowNew["LYSL2"] = row["LYSL2"];
                rowNew["LYSL3"] = row["LYSL3"];
                rowNew["LYSL4"] = row["LYSL4"];
                rowNew["JJMS"] = row["JJMS"];

                rowNew["JJSL1"] = row["JJSL1"];
                rowNew["JJSL2"] = row["JJSL2"];
                rowNew["JJSL3"] = row["JJSL3"];
                rowNew["JJSL4"] = row["JJSL4"];
                rowNew["GYSF"] = row["GYSF"];
                rowNew["QJSF"] = row["QJSF"];
                rowNew["ZJE"] = row["ZJE"];

                rowNew["LYSLXJ"] = row["LYSLXJ"];
                rowNew["JJSLXJ"] = row["JJSLXJ"];



                rowNew["LYGYSJ"] = row["LYGYSJ"];
                rowNew["LYSJ1"] = row["LYSJ1"];
                rowNew["LYSJ2"] = row["LYSJ2"];
                rowNew["LYSF0"] = row["LYSF0"];
                rowNew["LYSF1"] = row["LYSF1"];
                rowNew["LYSF2"] = row["LYSF2"];
                rowNew["LYSFXJ"] = row["LYSFXJ"];

                rowNew["JJGYSJ"] = row["JJGYSJ"];
                rowNew["JJSJ1"] = row["JJSJ1"];
                rowNew["JJSJ2"] = row["JJSJ2"];
                rowNew["JJSF0"] = row["JJSF0"];
                rowNew["JJSF1"] = row["JJSF1"];
                rowNew["JJSF2"] = row["JJSF2"];
                rowNew["JJSFXJ"] = row["JJSFXJ"];


                dtResult.Rows.Add(rowNew);
            }
            //返回希望的结果
            return dtResult;
        }


        public string GetZname(string strZid)
        {
            DataTable DT = new JH_Auth_BranchB().GetDTByCommand("SELECT * FROM jh_auth_branch WHERE DeptCode='" + strZid + "'");
            return DT.Rows[0]["DeptName"].ToString();
        }
    }
}